import pymysql
import requests
import json
import pandas as pd

def insert_data(conn, cursor, data):
    """
    将提取的数据插入到数据库中
    :param conn:数据库连接
    :param cursor:数据库游标
    :param data:要插入的数据
    """
    sql = "INSERT INTO food_info (food_name, food_cat, low_price, high_price, aver_price, place, unit_info, pub_time) " \
          "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
    values = data.values.tolist()
    cursor.executemany(sql, values)
    conn.commit()


def spider_main(limit, current, start_time, end_time):
    """
    请求主页面，返回响应内容
    :param page: 请求的页码
    :return: 响应的json内容
    """
    url = 'http://www.xinfadi.com.cn/getPriceData.html'

    # 伪装请求头
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/91.0.4472.77 Safari/537.36',
        'Referer': 'http://www.xinfadi.com.cn/',
        'Accept-Language': 'en-US,en;q=0.9',
    }

    # 查询字符串参数
    params = {
        'limit': limit,
        'current': current,
        'pubDateStartTime': start_time,
        'pubDateEndTime': end_time,
        'prodPcatid': 1186,
        'prodCatid': 1199,
        'prodName': ''
    }

    # 发送请求
    response = requests.get(url, headers=headers, params=params)
    print(response.status_code)

    # 获取响应内容
    content = response.text
    # 解析JSON字符串
    data = json.loads(content)
    return data


def send_req(current):
    # 发送请求，打印响应内容。
    limit = 20
    current = current
    start_time = "2015/03/01"
    end_time = "2024/03/12"
    data = spider_main(limit, current, start_time, end_time)

    # 连接到数据库
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='food_price')

    # 提取响应内容中的相关字段
    for food in data['list']:
        # 菜名
        food_name = food['prodName']
        # 菜名所属类型
        food_cat = food['prodCat']

        # 最低价
        low_price = food['lowPrice']

        # 最高价
        high_price = food['highPrice']

        # 平均价格
        aver_price = food['avgPrice']

        # 产地
        place = food['place']

        # 单位
        unit_info = food['unitInfo']

        # 价格发布时间
        pub_time = food['pubDate']

        # 将字段数据添加到DataFrame中
        food_info = pd.DataFrame({
            'food_name': [food_name],
            'food_cat': [food_cat],
            'low_price': [low_price],
            'high_price': [high_price],
            'aver_price': [aver_price],
            'place': [place],
            'unit_info': [unit_info],
            'pub_time': [pub_time]
        })

        # 将数据插入到数据库中
        cursor = conn.cursor()
        insert_data(conn, cursor, food_info)
        cursor.close()

    conn.close()


# 创建空的DataFrame
food_info = pd.DataFrame(
    columns=['food_name', 'food_cat', 'low_price', 'high_price', 'aver_price', 'place', 'unit_info', 'pub_time'])

for i in range(1, 766):
    send_req(i)
    print(f"------------------------第{i}页爬取完毕----------------------")

print("数据插入完成")

